Oracle中如何查找未使用绑定变量的SQL语句？

Oracle中如何查找未使用绑定变量的SQL语句？

利用V$SQL 视图的 FORCE_MATCHING_SIGNATURE 字段可以识别可能从绑定变量或 CURSOR_SHARING 获益的 SQL 语句。如果 SQL 已使用绑定变量或者 CURSOR_SHARING ，那么 FORCE_MATCHING_SIGNATURE 在对其进行标识时将给出同样的签名。换句话说，如果两个 SQL语句除了字面量的值之外都是相同的，它们将拥有相同的 FORCE_MATCHING_SIGNATURE ，这意味着如果为它们提供了绑定变量或者CURSOR_SHARING ，它们就成了完全相同的语句。所以，使用 FORCE_MATCHING_SIGNATURE 字段可以识别没有使用绑定变来的 SQL 语句。

可以使用如下的SQL 语句来查询：

select v.sql_id, v.sql_text, v.parsing_schema_name, fm.mathces, fm.ranking

  from force_mathces fm, v$sql v

 where fm.max_sql_child = (v.sql_id || v.child_number)

   and fm.ranking select v.sql_text, v.sql_id, v.force_matching_signature

  2    from v$sql v

  3   where v.sql_text like 'select e.ename,e.sal from emp e where e.empno %';

SQL_TEXT                                                      SQL_ID        FORCE_MATCHING_SIGNATURE

---------------------------------------------------------   -------------- ------------------------

select e.ename,e.sal from emp e where e.empno =7782         766syjydcn5fh      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7499         6ymy4hcb386vt      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7934         3t96y707p8by7      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7902         f9pyzxf7tnuzw      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7654         fvk1fzmrvjc4j      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7788         gsmatg9f4jd2z      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7566         4q9pzzpvvdpuu      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7369         3xhqmvm5vdqy0      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7698         bjjjw0gzaprzv      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7876         8nd8v8mrzxw4w      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7521         5tnyy066zfk1b      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7844         4kd7jb013g2zz      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7900         grx9sh4fwrcwx      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =:empno       20wmyr4cvrr6k      3.49355109645567E18

select e.ename,e.sal from emp e where e.empno =7839         6u2ajyu05gw9s      1.27397653964533E19

在v$sql视图中，发现使用字面量的SQL语句有14条，而使用绑定变量的SQL语句只有一条。其中使用字面量的SQL语句除以了字面量值不同之外，其他部分都是相同。而FORCE_MATCHING_SIGNATURE的值是在假设该SQL语句使用绑定变量或者CURSOR_SHARING得到的，因此通过FORCE_MATCHING_SIGNATURE字段识别没有绑定变量的SQL语句。

从10G开始可以通过如下方式查找未使用绑定变量的语句 select FORCE_MATCHING_SIGNATURE, count(1)   from v$sql  where FORCE_MATCHING_SIGNATURE > 0    and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE  group by FORCE_MATCHING_SIGNATURE having count(1) > &a  order by 2;  10G以上通过如下过程可以查找对未使用绑定变量的语句 create table shsnc.long_sql（sql_text clob, FORCE_MATCHING_SIGNATURE  number,count number) create or replace procedure query_sql is       cursor fms is select FORCE_MATCHING_SIGNATURE as fms, count(1) as count   from v$sql  where FORCE_MATCHING_SIGNATURE > 0    and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE  group by FORCE_MATCHING_SIGNATURE having count(1) > 100  order by 2;  v_fms number; v_sql01 varchar2(3999); v_sql02 varchar2(3999); begin for i in fms loop v_sql01:='insert into shsnc.long_sql(FORCE_MATCHING_SIGNATURE,sql_text)  select FORCE_MATCHING_SIGNATURE,sql_fulltext from (select FORCE_MATCHING_SIGNATURE,sql_fulltext from v$sql where FORCE_MATCHING_SIGNATURE='||i.fms||' and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from shsnc.long_sql)) where rownum